/*
 * FindFreeConnectionsCommand.java
 *
 * Created on October 3, 2007, 4:42 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package drb;

import java.io.BufferedReader;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;

/**
 *
 * @author Asger
 */
public class FindFreeConnectionsCommand extends PromptingCommand {
    
    private ArrayList<TrainRoute> selectedRoutes = new ArrayList<TrainRoute>();
    private boolean selectedFirstClass;
    
    public ArrayList<TrainRoute> getSelectedRoutes()
    {
        return selectedRoutes;
    }
    
    public boolean isFirstClassSelected()
    {
        return selectedFirstClass;
    }
    
    /** Creates a new instance of FindFreeConnectionsCommand */
    public FindFreeConnectionsCommand(BufferedReader input) {
        super(input);
    }
    
    public void execute() throws SQLException {
        String dateString;
        
        long departure = promptDateTime("When would you like to leave the earliest?", getTimeNow());
        long arrival = promptDateTime("When would you like to arrive the latest?", getTimeNow() + ONE_DAY);
        String from = "";
        String to = "";
        while (from.equals(to))
        {
            from = promptStation("What station would you like to leave from?", connection, false);
            to = promptStation("What station would you like to travel to?", connection, false);
            if (from.equals(to))
                System.err.println("You need no trains to travel from " + from + " to " + to + ". Please enter two DIFFERENT station names:");
        }
        boolean firstClass = promptYesNo("Would you like to travel first class?", false);
        boolean bedWagon = promptYesNo("Would you like to travel with a bed-wagon?", false);
        
        // Print summary
        System.out.println("-------------------------------");
        System.out.println(" Summary of criteria:");
        System.out.println(" Earliest departure:  " + formattedDateTime(departure));
        System.out.println(" Latest arrival:      " + formattedDateTime(arrival));
        System.out.println(" From:                " + from);
        System.out.println(" To:                  " + to );
        System.out.println(" Class:               " + (firstClass? "First class":"Economic class"));
        System.out.println(" Wagon type:          " + (bedWagon? "Bed wagon":"Standard wagon"));
        System.out.println("-------------------------------");
        
        // Get the routes
        ArrayList<TrainRoute> routes = getRoutesBetweenStations(
                departure,
                arrival,
                from,
                to,
                firstClass,
                bedWagon);
        
        if (routes.size() == 0)
        {
            System.out.println("Sorry, no connections matching your criteria were found.");
            System.out.println("Note: If you are having trouble, try calling 'apply-schedule' and try again.");
            if (firstClass || bedWagon)
                System.out.println("      Also, there are not many first-class and bed-wagons by default, so try using economic class and standard wagons instead.");
            System.out.println("-------------------------------");
        }
        else
        {
            System.out.println("We found " + routes.size() + " routes matching your criteria.");
            System.out.println("-------------------------------");
        }
        
        selectedFirstClass = firstClass;
        
        // Assign the currently selected routes.
        for (int i=0; i<routes.size(); i++)
        {
            routes.get(i).id = i + 1;
        }
        selectedRoutes = routes;
        
        // Print out the routes available.
        for (TrainRoute route : routes)
        {
            route.printDetails(firstClass);
            System.out.println("-------------------------------");
        }
    }
    
    private ArrayList<TrainRoute> getRoutesBetweenStations(
            long departure,
            long arrival,
            String from,
            String to,
            boolean firstClass,
            boolean bedWagon) throws SQLException
    {
        ArrayList<TrainRoute> routes = getRoutesBetweenStationsRec(departure, arrival, from, to, firstClass, bedWagon, true, new HashSet<String>());
        for (TrainRoute route : routes)
        {
            route.reverse();
        }
        return routes;
    }
    
    private ArrayList<TrainRoute> getRoutesBetweenStationsRec(
            long departure, 
            long arrival, 
            String from,
            String to,
            boolean firstClass,
            boolean bedWagon,
            boolean onlyTheBest,
            HashSet<String> ignoreStations) throws SQLException
    {
        // If we're already there, there are 0 connections in the route.
        if (from.equals(to))
        {
            ArrayList<TrainRoute> result = new ArrayList<TrainRoute>();
            result.add(new TrainRoute()); // Add an empty route.
            return result;
        }
        
        ignoreStations.add(from);
        char fcChar = firstClass? 'A':'B';
        char wgChar = bedWagon? 'B':'S';
        String sql = "SELECT * FROM connections c WHERE " +
                "c.departure >= " + departure + " AND " +       // Connection must be in desired time interval
                "c.arrival <= " + arrival + " AND " +           // 
                "c.from_name = '"+escapeSQL(from)+"' AND " +    // Must start in this station
                "EXISTS (SELECT * FROM seats s NATURAL JOIN transports t NATURAL JOIN wagons w WHERE " + // Must exist a free seat on this fare
                    "t.train_number=c.train_number AND " +      // Seats on same train
                    "t.departure=c.departure AND " +            // Fares departing at this time
                    "s.class='"+fcChar+"' AND " +               // Seats of the correct class
                    "w.wagon_type='"+wgChar+"' AND " +          // Wagons of the desired type (standard/bed)
                    "NOT EXISTS (SELECT * FROM reservations r WHERE " + // Must not exist a reservation for the seat at this time
                        "r.wagon_number=s.wagon_number AND " +      // Compare reservation to our seat/fare
                        "r.seat_row=s.seat_row AND " +
                        "r.seat_position=s.seat_position AND " +
                        "r.departure=t.departure)) " +
                "ORDER BY c.departure ASC";
        
        ArrayList<TrainRoute> routes = new ArrayList<TrainRoute>();
        ResultSet result = connection.createStatement().executeQuery(sql);
        while (result.next())
        {
            TrainConnection c = TrainConnection.parse(result);
            
            if (ignoreStations.contains(c.to))
                continue;
            
            // Get all routes available from this connection
            ArrayList<TrainRoute> subRoutes = getRoutesBetweenStationsRec(
                    c.arrival,
                    arrival,
                    c.to,
                    to,
                    firstClass,
                    bedWagon,
                    onlyTheBest,
                    (HashSet<String>)ignoreStations.clone());
            
            if (onlyTheBest)
            {
                if (subRoutes.size() > 0)
                {
                    TrainRoute subRoute = subRoutes.get(0);
                    subRoute.connections.add(c);
                    routes.add(subRoute);
                }
            }
            else
            {
                for (int i=0; i<subRoutes.size(); i++)
                {
                    subRoutes.get(i).connections.add(c);
                }
                routes.addAll(subRoutes);
            }
        }
        Collections.sort(routes);
        
        return routes;
    }
    
    
}









